#!/bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto
MYSQL_HOME=/usr/bin/mysql
HIVE_HOME=/usr/bin/hive

echo "" >> ./sqoop_liujie.log
start_time=`date +"%Y-%m-%d %H:%M:%S"`
echo "rpt insert table start ${start_time}" >> ./sqoop_liujie.log

${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
insert into hive.edu_rpt.rpt_stu_attendance
select class_id,
       studying_student_count,
       morning_attendance_count,
       morning_leave_count,
       morning_late_count,
       morning_truant_count,
       afternoon_attendance_count,
       afternoon_leave_count,
       afternoon_late_count,
       afternoon_truant_count,
       evening_attendance_count,
       evening_leave_count,
       evening_late_count,
       evening_truant_count,

       case when studying_student_count = 0  then null else
       cast(cast(morning_attendance_count as decimal(38,4)) / cast(studying_student_count as decimal(38,4)) * 100 as decimal(5,2))
       end as morning_attendance_ratio,

       case when studying_student_count = 0  then null else
       cast(cast(morning_late_count as decimal(38,4)) / cast(studying_student_count as decimal(38,4)) * 100 as decimal(5,2))
       end as morning_late_ratio,

       case when studying_student_count = 0  then null else
       cast(cast(morning_leave_count as decimal(38,4)) / cast(studying_student_count as decimal(38,4)) * 100 as decimal(5,2))
       end as morning_leave_ratio,

       case when studying_student_count = 0  then null else
       cast(cast(morning_truant_count as decimal(38,4)) / cast(studying_student_count as decimal(38,4)) * 100 as decimal(5,2))
       end as morning_truant_ratio,

       case when studying_student_count = 0  then null else
       cast(cast(afternoon_attendance_count as decimal(38,4)) / cast(studying_student_count as decimal(38,4)) * 100 as decimal(5,2))
       end as afternoon_attendance_ratio,

       case when studying_student_count = 0  then null else
       cast(cast(afternoon_late_count as decimal(38,4)) / cast(studying_student_count as decimal(38,4)) * 100 as decimal(5,2))
       end as afternoon_late_ratio,

       case when studying_student_count = 0  then null else
       cast(cast(afternoon_leave_count as decimal(38,4)) / cast(studying_student_count as decimal(38,4)) * 100 as decimal(5,2))
       end as afternoon_leave_ratio,

       case when studying_student_count = 0  then null else
       cast(cast(afternoon_truant_count as decimal(38,4)) / cast(studying_student_count as decimal(38,4)) * 100 as decimal(5,2))
       end as afternoon_truant_ratio,

       case when studying_student_count = 0  then null else
       cast(cast(evening_attendance_count as decimal(38,4)) / cast(studying_student_count as decimal(38,4)) * 100 as decimal(5,2))
       end as evening_attendance_ratio,

       case when studying_student_count = 0  then null else
       cast(cast(evening_late_count as decimal(38,4)) / cast(studying_student_count as decimal(38,4)) * 100 as decimal(5,2))
       end as evening_late_ratio,

       case when studying_student_count = 0  then null else
       cast(cast(evening_leave_count as decimal(38,4)) / cast(studying_student_count as decimal(38,4)) * 100 as decimal(5,2))
       end as evening_leave_ratio,

       case when studying_student_count = 0  then null else
       cast(cast(evening_truant_count as decimal(38,4)) / cast(studying_student_count as decimal(38,4)) * 100 as decimal(5,2))
       end as evening_truant_ratio,

       studying_date

from hive.edu_dws.dws_stu_attendance_day_count t;
"



start_time=`date +"%Y-%m-%d %H:%M:%S"`
echo "rpt insert table end ${start_time}" >> ./sqoop_liujie.log
